package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.http.HttpSession;

import model.PageBean;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import common.Constants;

import util.JdbcUtil;
import util.JsonUtil;
import exception.SystemException;

public class TableInfoDao {

	private static final Log log = LogFactory.getLog(TableInfoDao.class);
	
	public JSONArray findAllTables(HttpSession session, String keyword, PageBean pageBean)
			throws SystemException {
		Connection conn = JdbcUtil.getConnection(session);
		int from = pageBean.getStart() + 1;
		int to = pageBean.getPage() * pageBean.getRows();
		String sql = to == 0 ?  querySql(keyword) :querySql(keyword, from, to);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int getTablesCount(HttpSession session, String keyword) throws SystemException {
		Connection conn = JdbcUtil.getConnection(session);
		String sql = countSql(keyword);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt("count");
			} else {
				return 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
	// 拼接查询的sql语句
	private String querySql(String keyword) {
		JSONArray tables = getMaskedTables();
//		StringBuffer sb = new StringBuffer(
//				"select rownum rn,owner,table_name tab_name,num_rows,(select sum(bytes)/1024/1024 from dba_extents where owner=m.OWNER and segment_name=m.TABLE_NAME) memory from dba_tables m where (select count(*) from optim.modudf_mask where upper(owner)=m.OWNER and upper(tab_name)=m.TABLE_NAME) > 0 and num_rows > 0 order by memory desc ");
		StringBuffer sb = new StringBuffer(
				"select rownum rn,owner,table_name tab_name,num_rows,(select sum(bytes)/1024/1024 from dba_extents where owner=m.OWNER and segment_name=m.TABLE_NAME) memory from dba_tables m where ");
		if (tables.size() > 0) {
			StringBuffer condition = new StringBuffer();
			condition.append("(m.owner,m.table_name) in (");
			for (int i = 0; i < tables.size(); i++) {
				JSONObject table = tables.getJSONObject(i);
				condition.append("(upper('" + table.getString("OWNER") + "'),upper('" + table.getString("TAB_NAME") + "'))");
				if (i != tables.size() - 1) {
					condition.append(",");
				}
			}
			sb.append(condition.toString());
			sb.append(")");
			sb.append(conditionSql(keyword));
			sb.append(" and num_rows > 0 order by num_rows desc");
		} else {
			sb.append("1!=1");
		}
		return sb.toString();
	}

	// 拼接查询码内建视图的sql语句
	private String querySql(String keyword, int from, int to) {
		StringBuffer sb = new StringBuffer("select t.* from (");
		sb.append(this.querySql(keyword)).append(")t where t.rn between ")
				.append(from).append(" and ").append(to);
		return sb.toString();
	}

	// 拼接查询总条数的sql语句
	private String countSql(String keyword) {
//		StringBuffer sb = new StringBuffer(
//				"select count(*) count from  dba_tables m where (select count(*) from optim.modudf_mask where upper(owner)=m.OWNER and upper(tab_name)=m.TABLE_NAME) > 0 and num_rows > 0 ");
//		sb.append(conditionSql(keyword));
		JSONArray tables = getMaskedTables();
		StringBuffer sb = new StringBuffer(
				"select count(*) count from  dba_tables m where ");
		if (tables.size() > 0) {
			StringBuffer condition = new StringBuffer();
			condition.append("(m.owner,m.table_name) in (");
			for (int i = 0; i < tables.size(); i++) {
				JSONObject table = tables.getJSONObject(i);
				condition.append("(upper('" + table.getString("OWNER") + "'),upper('" + table.getString("TAB_NAME") + "'))");
				if (i != tables.size() - 1) {
					condition.append(",");
				}
			}
			sb.append(condition.toString());
			sb.append(")");
			sb.append(conditionSql(keyword));
			sb.append(" and num_rows > 0");
		} else {
			sb.append("1!=1");
		}
		return sb.toString();
	}
	
	private String conditionSql(String keyword) {
		if (keyword != null && keyword.length() > 0) {
			StringBuffer sb = new StringBuffer();
			sb.append(" and (m.owner like '%").append(keyword).append("%'");
			sb.append(" or m.table_name like '%").append(keyword).append("%'");
			sb.append(" or m.num_rows like '%").append(keyword).append("%')");
			return sb.toString();
		}
		return "";
	}
	
	private JSONArray getMaskedTables()
			throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "select distinct owner, tab_name from optim.modudf_mask";
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
	public static void main(String[] args) {
		Connection conn = JdbcUtil.getConnection(Constants.ORACLE_DB_URL,"modudf_test","modudf_test");
		TableInfoDao dao = new TableInfoDao();
		String sql = dao.countSql("");
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			System.out.println(JsonUtil.formatRsToJsonArray(resultSet));
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
	
}
